﻿-- ==========================================================================================
-- Author:		Patrick <yuanjie@shanghai3h.com>
-- Create date: 2011-1-24
-- Description:	The clr assembly of DataCenter database deploy script. 
--				This script can be executed just by sqlcmd utility. The section below is the 
--				definition of variable used in the script, which should be set by using the
--				argument -v in sqlcmd.
-- Variable:	db_DataCenter_Name		The name of DataCenter database.
--				Assembly_Path			The clr assembly file path.
-- ==========================================================================================

-- *****************************************************
-- Run necessary initialization
-- *****************************************************
EXEC sp_configure 'clr enable', 1;
GO
RECONFIGURE;
GO

USE [$(db_DataCenter_Name)]
GO

EXEC sp_changedbowner 'sa';
GO
ALTER DATABASE [$(db_DataCenter_Name)] SET TRUSTWORTHY ON;
GO

-- *****************************************************
-- Delete old procedures
-- *****************************************************
IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'AggregateValue') 
	BEGIN
		DROP PROCEDURE [AggregateValue];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 AggregateValue 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 AggregateValue 成功'
	END
GO

IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'CalculateValue') 
	BEGIN
		DROP PROCEDURE [CalculateValue];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 CalculateValue 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 CalculateValue 成功'
	END
GO

IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'DropValue') 
	BEGIN
		DROP PROCEDURE [DropValue];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 DropValue 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 DropValue 成功'
	END
GO

IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'ImportValue')
	BEGIN
		DROP PROCEDURE [ImportValue];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 ImportValue 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 ImportValue 成功'
	END
GO

IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'InsertValue') 
	BEGIN
		DROP PROCEDURE [InsertValue];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 InsertValue 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 InsertValue 成功'
	END
GO

IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'ProcessValue') 
	BEGIN
		DROP PROCEDURE [ProcessValue];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 ProcessValue 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 ProcessValue 成功'
	END
GO

IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'QueryRecentValue') 
	BEGIN
		DROP PROCEDURE [QueryRecentValue];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 QueryRecentValue 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 QueryRecentValue 成功'
	END
GO

IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'QueryLastValue') 
	BEGIN
		DROP PROCEDURE [QueryLastValue];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 QueryLastValue 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 QueryLastValue 成功'
	END
GO

IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'QueryObjectInfo') 
	BEGIN
		DROP PROCEDURE [QueryObjectInfo];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 QueryObjectInfo 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 QueryObjectInfo 成功'
	END
GO

IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'QueryValue') 
	BEGIN
		DROP PROCEDURE [QueryValue];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 QueryValue 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 QueryValue 成功'
	END
GO

IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'QueryScadaValue') 
	BEGIN
		DROP PROCEDURE [QueryValue];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 QueryScadaValue 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 QueryScadaValue 成功'
	END
GO

IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'RefreshSetting') 
	BEGIN
		DROP PROCEDURE [RefreshSetting];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 RefreshSetting 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 RefreshSetting 成功'
	END
GO

-- *****************************************************
-- Delete old functions
-- *****************************************************
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'SelectObjectInfo') 
	BEGIN
		DROP FUNCTION [SelectObjectInfo];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 SelectObjectInfo 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 SelectObjectInfo 成功'
	END
GO

IF EXISTS (SELECT name FROM sys.objects WHERE name = 'SelectValue') 
	BEGIN
		DROP FUNCTION [SelectValue];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 SelectValue 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 SelectValue 成功'
	END
GO

IF EXISTS (SELECT name FROM sys.objects WHERE name = 'SelectRecentValue') 
	BEGIN
		DROP FUNCTION [SelectRecentValue];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 SelectRecentValue 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 SelectRecentValue 成功'
	END
GO

IF EXISTS (SELECT name FROM sys.objects WHERE name = 'GetVersion')
	BEGIN
		DROP FUNCTION [GetVersion];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 GetVersion 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 GetVersion 成功'
	END
GO

IF EXISTS (SELECT name FROM sys.objects WHERE name = 'ConvertToDate') 
	BEGIN
		DROP FUNCTION [ConvertToDate];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除存储过程 ConvertToDate 失败'
		ELSE PRINT '[SUCCESS]	删除存储过程 ConvertToDate 成功'
	END
GO

-- *****************************************************
-- Delete old assemblies
-- *****************************************************
IF EXISTS (SELECT NAME FROM sys.assemblies WHERE NAME = 'DataCenter.Clr') 
	BEGIN
		DROP ASSEMBLY [DataCenter.Clr];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除程序集 DataCenter.Clr 失败'
		ELSE PRINT '[SUCCESS]	删除程序集 DataCenter.Clr 成功'
	END
GO

IF EXISTS (SELECT NAME FROM sys.assemblies WHERE NAME = 'DataCenter.Share') 
	BEGIN
		DROP ASSEMBLY [DataCenter.Share];

		IF @@ERROR <> 0 PRINT '[ERROR]	删除程序集 DataCenter.Share 失败'
		ELSE PRINT '[SUCCESS]	删除程序集 DataCenter.Share 成功'
	END
GO

-- *****************************************************
-- Create new assemblies
-- *****************************************************
CREATE ASSEMBLY [DataCenter.Share]
AUTHORIZATION [dbo]
FROM '$(Assembly_Path)\DataCenter.Share.dll'
WITH PERMISSION_SET = UNSAFE;
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建程序集 DataCenter.Share 失败'
ELSE PRINT '[SUCCESS]	新建程序集 DataCenter.Share 成功'
GO

CREATE ASSEMBLY [DataCenter.Clr]
AUTHORIZATION [dbo]
FROM '$(Assembly_Path)\DataCenter.Clr.dll'
WITH PERMISSION_SET = UNSAFE;
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建程序集 DataCenter.Clr 失败'
ELSE PRINT '[SUCCESS]	新建程序集 DataCenter.Clr 成功'
GO

-- *****************************************************
-- Create new procedures
-- *****************************************************
CREATE PROCEDURE [dbo].[AggregateValue]
	@ObjectId NVARCHAR (4000), 
	@ValueType NVARCHAR (4000), 
	@OperType NVARCHAR (4000), 
	@StartTime DATETIME, 
	@EndTime DATETIME, 
	@Step INT, 
	@GroupStyle NVARCHAR (4000), 
	@AggregateStyle NVARCHAR (4000), 
	@Parameters NVARCHAR (4000)
AS EXTERNAL NAME [DataCenter.Clr].[StoredProcedures].[AggregateValue]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建存储过程 AggregateValue 失败'
ELSE PRINT '[SUCCESS]	新建存储过程 AggregateValue 成功'
GO

CREATE PROCEDURE [dbo].[CalculateValue]
	@ObjectId NVARCHAR (4000), 
	@StartTime DATETIME, 
	@EndTime DATETIME
AS EXTERNAL NAME [DataCenter.Clr].[StoredProcedures].[CalculateValue]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建存储过程 CalculateValue 失败'
ELSE PRINT '[SUCCESS]	新建存储过程 CalculateValue 成功'
GO

CREATE PROCEDURE [dbo].[DropValue]
	@ObjectId NVARCHAR (4000), 
	@ValueType NVARCHAR (4000), 
	@StartTime DATETIME, 
	@EndTime DATETIME
AS EXTERNAL NAME [DataCenter.Clr].[StoredProcedures].[DropValue]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建存储过程 DropValue 失败'
ELSE PRINT '[SUCCESS]	新建存储过程 DropValue 成功'
GO

CREATE PROCEDURE [dbo].[ImportValue]
	@ObjectId NVARCHAR (4000), 
	@StartTime DATETIME, 
	@EndTime DATETIME
AS EXTERNAL NAME [DataCenter.Clr].[StoredProcedures].[ImportValue]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建存储过程 ImportValue 失败'
ELSE PRINT '[SUCCESS]	新建存储过程 ImportValue 成功'
GO

CREATE PROCEDURE [dbo].[InsertValue]
	@ObjectId INT, 
	@ValueType INT, 
	@OperType INT, 
	@ValueTime DATETIME, 
	@Value REAL
AS EXTERNAL NAME [DataCenter.Clr].[StoredProcedures].[InsertValue]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建存储过程 InsertValue 失败'
ELSE PRINT '[SUCCESS]	新建存储过程 InsertValue 成功'
GO

CREATE PROCEDURE [dbo].[ProcessValue]
	@ObjectId NVARCHAR (4000), 
	@StartTime DATETIME, 
	@EndTime DATETIME
AS EXTERNAL NAME [DataCenter.Clr].[StoredProcedures].[ProcessValue]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建存储过程 ProcessValue 失败'
ELSE PRINT '[SUCCESS]	新建存储过程 ProcessValue 成功'
GO

CREATE PROCEDURE [dbo].[QueryLastValue]
	@ObjectId NVARCHAR (4000), 
	@ValueType NVARCHAR (4000)
AS EXTERNAL NAME [DataCenter.Clr].[StoredProcedures].[QueryLastValue]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建存储过程 QueryLastValue 失败'
ELSE PRINT '[SUCCESS]	新建存储过程 QueryLastValue 成功'
GO

CREATE PROCEDURE [dbo].[QueryRecentValue]
	@ObjectId [nvarchar](4000),
	@ValueType [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DataCenter.Clr].[StoredProcedures].[QueryRecentValue]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建存储过程 QueryRecentValue 失败'
ELSE PRINT '[SUCCESS]	新建存储过程 QueryRecentValue 成功'
GO

CREATE PROCEDURE [dbo].[QueryObjectInfo]
	@ObjectId NVARCHAR (4000), 
	@ObjectType NVARCHAR (4000)
AS EXTERNAL NAME [DataCenter.Clr].[StoredProcedures].[QueryObjectInfo]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建存储过程 QueryObjectInfo 失败'
ELSE PRINT '[SUCCESS]	新建存储过程 QueryObjectInfo 成功'
GO

CREATE PROCEDURE [dbo].[QueryValue]
	@ObjectId NVARCHAR (4000), 
	@ValueType NVARCHAR (4000), 
	@OperType NVARCHAR (4000), 
	@StartTime DATETIME, 
	@EndTime DATETIME, 
	@Step INT, 
	@ImportValue INT
AS EXTERNAL NAME [DataCenter.Clr].[StoredProcedures].[QueryValue]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建存储过程 QueryValue 失败'
ELSE PRINT '[SUCCESS]	新建存储过程 QueryValue 成功'
GO

CREATE PROCEDURE [dbo].[RefreshSetting]
AS EXTERNAL NAME [DataCenter.Clr].[StoredProcedures].[RefreshSetting]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建存储过程 RefreshSetting 失败'
ELSE PRINT '[SUCCESS]	新建存储过程 RefreshSetting 成功'
GO

CREATE FUNCTION [dbo].[SelectObjectInfo]
	(@ObjectId NVARCHAR (4000), @ObjectType NVARCHAR (4000))
RETURNS 
	 TABLE (
		[ObjectId]   INT            NULL,
		[ObjectType] INT            NULL,
		[ObjectName] NVARCHAR (255) NULL)
AS
EXTERNAL NAME [DataCenter.Clr].[UserDefinedFunctions].[SelectObjectInfo]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建函数 SelectObjectInfo 失败'
ELSE PRINT '[SUCCESS]	新建函数 SelectObjectInfo 成功'
GO

CREATE FUNCTION [dbo].[SelectValue]
(@ObjectId NVARCHAR (4000), @ValueType NVARCHAR (4000), @OperType NVARCHAR (4000), @StartTime DATETIME, @EndTime DATETIME, @Step INT, @ImportValue INT)
RETURNS 
	 TABLE (
		[ObjectId]  INT      NULL,
		[ValueType] INT      NULL,
		[ValueTime] DATETIME NULL,
		[OperType]  INT      NULL,
		[R__0]      REAL     NULL)
AS
EXTERNAL NAME [DataCenter.Clr].[UserDefinedFunctions].[SelectValue]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建函数 Selectvalue 失败'
ELSE PRINT '[SUCCESS]	新建函数 Selectvalue 成功'
GO

CREATE FUNCTION [dbo].[SelectRecentValue](@ObjectId [nvarchar](4000), @ValueType [nvarchar](4000))
RETURNS  TABLE (
	[ObjectId] [int] NULL,
	[ValueType] [int] NULL,
	[ValueTime] [datetime] NULL,
	[R__0] [real] NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [DataCenter.Clr].[UserDefinedFunctions].[SelectRecentValue]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建函数 SelectRecentValue 失败'
ELSE PRINT '[SUCCESS]	新建函数 SelectRecentValue 成功'
GO

CREATE FUNCTION [dbo].[GetVersion]( )
RETURNS NVARCHAR (4000)
AS
EXTERNAL NAME [DataCenter.Clr].[UserDefinedFunctions].[GetVersion]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建函数 GetVersion 失败'
ELSE PRINT '[SUCCESS]	新建函数 GetVersion 成功'
GO

CREATE FUNCTION [dbo].[ConvertToDate]
(@DateTimeValue DATETIME)
RETURNS DATETIME
AS
EXTERNAL NAME [DataCenter.Share].[UserDefinedFunctions].[ConvertToDate]
GO

IF @@ERROR <> 0 PRINT '[ERROR]	新建函数 ConvertToDate 失败'
ELSE PRINT '[SUCCESS]	新建函数 ConvertToDate 成功'
GO

-- 更新App所在文件路径
UPDATE DataCenterSetting SET AppFilePath = '$(Assembly_Path)DataCenter.App.exe'